﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace CTManagerSoln
{
    class ClsUserphongban
    {
        internal static int Isert(string username, string phongban, bool kiemduyet)
        {
            int stateUpdate;
            string sql = "INSERT into tblUserPhongban (UserName,MaPhongBan,QuyenKiemDuyet) Values(@UserName,@MaPhongBan,@QuyenKiemDuyet)";

            using (OleDbConnection conn = new OleDbConnection(Commons.DBConnect.ConnectionString))
            {
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    conn.Open();
                    command.Parameters.Add("@UserName", OleDbType.VarChar).Value = username;
                    command.Parameters.Add("@MaPhongBan", OleDbType.VarChar).Value = phongban;
                    command.Parameters.Add("@QuyenKiemDuyet", OleDbType.Boolean).Value = kiemduyet;
                    stateUpdate = command.ExecuteNonQuery();
                }
            }
            return stateUpdate;
        }

        internal static DataTable GetallUserPhongBan()
        {
            string sql = "SELECT tblUserPhongban.UserName,tblUserPhongban.MaPhongBan,tblUsers.FullName,tblPhongban.TenPhongBan,tblUserPhongban.QuyenKiemDuyet"
+" FROM (tblPhongban INNER JOIN tblUserPhongban ON tblPhongban.MaPhongBan = tblUserPhongban.MaPhongBan) INNER JOIN tblUsers ON tblUserPhongban.UserName = tblUsers.UserName;";
            DataTable dtReturn = new DataTable();
            OleDbCommand cmd = new OleDbCommand(sql, Commons.DBConnect);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(dtReturn);
            return dtReturn;
        }

        internal static DataTable getUserPhongbanbyID(string username, string phongban)
        {
            string sql = "SELECT tblUserPhongban.UserName,tblUserPhongban.MaPhongBan,tblUsers.FullName,tblPhongban.TenPhongBan"
            + " FROM (tblPhongban INNER JOIN tblUserPhongban ON tblPhongban.MaPhongBan = tblUserPhongban.MaPhongBan) INNER JOIN tblUsers ON tblUserPhongban.UserName = tblUsers.UserName"
            + " where tblUserPhongban.UserName='" + username + "' and  tblUserPhongban.MaPhongBan ='" + phongban + "' and tblUsers.isDelete=False;";
            DataTable dtReturn = new DataTable();
            OleDbCommand cmd = new OleDbCommand(sql, Commons.DBConnect);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(dtReturn);
            return dtReturn;
        }
        internal static DataTable getUserPhongbanbyID(string username)
        {
            string sql = "SELECT tblUserPhongban.UserName,tblUserPhongban.MaPhongBan,tblUserPhongban.QuyenKiemDuyet,tblUsers.FullName,tblPhongban.TenPhongBan"
            + " FROM (tblPhongban INNER JOIN tblUserPhongban ON tblPhongban.MaPhongBan = tblUserPhongban.MaPhongBan) INNER JOIN tblUsers ON tblUserPhongban.UserName = tblUsers.UserName"
            + " where tblUserPhongban.UserName='" + username + "' and tblUsers.isDelete=False;";
            DataTable dtReturn = new DataTable();
            OleDbCommand cmd = new OleDbCommand(sql, Commons.DBConnect);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(dtReturn);
            return dtReturn;
        }

        internal static DataTable getUserKiemDuyet(string username)
        {
            string sql = string.Format("select * from tblUserPhongban where QuyenKiemDuyet=true and MaPhongBan in (select MaPhongBan from tblUserPhongban where UserName='{0}');",username);
            DataTable dtReturn = new DataTable();
            OleDbCommand cmd = new OleDbCommand(sql, Commons.DBConnect);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(dtReturn);
            return dtReturn;
        }
    }
}
